<?php
class ModelMenuFood extends Model {
	public function updateViewed($food_id) {
		$this->db->query("UPDATE " . DB_PREFIX . "food SET viewed = (viewed + 1) WHERE food_id = '" . (int)$food_id . "'");
	}
	
	public function getFood($food_id) {
		if ($this->customer->isLogged()) {
			$customer_group_id = $this->customer->getCustomerGroupId();
		} else {
			$customer_group_id = $this->config->get('config_customer_group_id');
		}	
				
		$query = $this->db->query("SELECT DISTINCT *, fd.name AS name, f.image, (SELECT price FROM " . DB_PREFIX . "food_special fs WHERE fs.food_id = f.food_id AND ((fs.date_start = '0000-00-00' OR fs.date_start < NOW()) AND (fs.date_end = '0000-00-00' OR fs.date_end > NOW())) ORDER BY fs.priority ASC, fs.price ASC LIMIT 1) AS special, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "food_review r1 WHERE r1.food_id = f.food_id AND r1.status = '1' GROUP BY r1.food_id) AS rating, (SELECT COUNT(*) AS total FROM " . DB_PREFIX . "food_review r2 WHERE r2.food_id = f.food_id AND r2.status = '1' GROUP BY r2.food_id) AS reviews, f.sort_order FROM " . DB_PREFIX . "food f LEFT JOIN " . DB_PREFIX . "food_description fd ON (f.food_id = fd.food_id) WHERE f.restaurant_id = '" . (int)$this->config->get('config_restaurant_id') . "' AND f.food_id = '" . (int)$food_id . "' AND fd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND f.status = '1' AND f.date_available <= NOW()");
		
		if ($query->num_rows) {
			$query->row['price'] = $query->row['price'];
			$query->row['rating'] = (int)$query->row['rating'];
			
			return $query->row;
		} else {
			return false;
		}
	}

	public function getFoods($data = array()) {
		$cache = md5(http_build_query($data));
		
		$food_data = $this->cache->get('food.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_restaurant_id') . '.' . $cache);
		
		if (!$food_data) {
			$sql = "SELECT f.food_id, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "food_review r1 WHERE r1.food_id = f.food_id AND r1.status = '1' GROUP BY r1.food_id) AS rating FROM " . DB_PREFIX . "food f LEFT JOIN " . DB_PREFIX . "food_description fd ON (f.food_id = fd.food_id)"; 
			
			if (!empty($data['filter_tag'])) {
				$sql .= " LEFT JOIN " . DB_PREFIX . "food_tag mt ON (f.food_id = mt.food_id)";			
			}
						
			if (!empty($data['filter_menu_id'])) {
				$sql .= " LEFT JOIN " . DB_PREFIX . "food_to_menu f2m ON (f.food_id = f2m.food_id)";			
			}
			
			$sql .= " WHERE f.restaurant_id = '" . (int)$this->config->get('config_restaurant_id') . "' AND fd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND f.status = '1' AND f.date_available <= NOW()"; 
			
			if (!empty($data['filter_name']) || !empty($data['filter_tag'])) {
				$sql .= " AND (";
											
				if (!empty($data['filter_name'])) {
					$implode = array();
					
					$words = explode(' ', $data['filter_name']);
					
					foreach ($words as $word) {
						if (!empty($data['filter_description'])) {
							$implode[] = "LCASE(fd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%' OR LCASE(fd.description) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
						} else {
							$implode[] = "LCASE(fd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
						}				
					}
					
					if ($implode) {
						$sql .= " " . implode(" OR ", $implode) . "";
					}
				}
				
				if (!empty($data['filter_name']) && !empty($data['filter_tag'])) {
					$sql .= " OR ";
				}
				
				if (!empty($data['filter_tag'])) {
					$implode = array();
					
					$words = explode(' ', $data['filter_tag']);
					
					foreach ($words as $word) {
						$implode[] = "LCASE(mt.tag) LIKE '%" . $this->db->escape(utf8_strtolower($data['filter_tag'])) . "%' AND mt.language_id = '" . (int)$this->config->get('config_language_id') . "'";
					}
					
					if ($implode) {
						$sql .= " " . implode(" OR ", $implode) . "";
					}
				}
			
				$sql .= ")";
			}
			
			if (!empty($data['filter_menu_id'])) {
				if (!empty($data['filter_sub_menu'])) {
					$implode_data = array();
					
					$implode_data[] = "f2m.menu_id = '" . (int)$data['filter_menu_id'] . "'";
					
					$this->load->model('menu/menu');
					
					$categories = $this->model_menu_menu->getMenusByParentId($data['filter_menu_id']);
										
					foreach ($categories as $food_id) {
						$implode_data[] = "f2m.food_id = '" . (int)$food_id . "'";
					}
								
					$sql .= " AND (" . implode(' OR ', $implode_data) . ")";			
				} else {
					$sql .= " AND f2m.menu_id = '" . (int)$data['filter_menu_id'] . "'";
				}
			}		
			
			$sql .= " GROUP BY f.food_id";
			
			$sort_data = array(
				'fd.name',
				'f.price',
				'rating',
				'f.sort_order',
				'f.date_added'
			);	
			
			if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
				if ($data['sort'] == 'fd.name' || $data['sort'] == 'f.model') {
					$sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
				} else {
					$sql .= " ORDER BY " . $data['sort'];
				}
			} else {
				$sql .= " ORDER BY f.sort_order";	
			}
			
			if (isset($data['order']) && ($data['order'] == 'DESC')) {
				$sql .= " DESC";
			} else {
				$sql .= " ASC";
			}
		
			if (isset($data['start']) || isset($data['limit'])) {
				if ($data['start'] < 0) {
					$data['start'] = 0;
				}				
	
				if ($data['limit'] < 1) {
					$data['limit'] = 20;
				}	
			
				$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
			}
			
			$food_data = array();
					
			$query = $this->db->query($sql);
		
			foreach ($query->rows as $result) {
				$food_data[$result['food_id']] = $this->getFood($result['food_id']);
			}
			
			$this->cache->set('food.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_restaurant_id') . '.' . $cache, $food_data);
		}
		
		return $food_data;
	}
	
	public function getFoodSpecials($data = array()) {
		$sql = "SELECT DISTINCT fs.food_id, (SELECT AVG(rating) FROM " . DB_PREFIX . "food_review r1 WHERE r1.food_id = fs.food_id AND r1.status = '1' GROUP BY r1.food_id) AS rating FROM " . DB_PREFIX . "food_special fs LEFT JOIN " . DB_PREFIX . "food f ON (fs.food_id = f.food_id) LEFT JOIN " . DB_PREFIX . "food_description fd ON (f.food_id = fd.food_id) WHERE f.restaurant_id = '" . (int)$this->config->get('config_restaurant_id') . "' AND f.status = '1' AND f.date_available <= NOW() AND ((fs.date_start = '0000-00-00' OR fs.date_start < NOW()) AND (fs.date_end = '0000-00-00' OR fs.date_end > NOW())) GROUP BY fs.food_id";

		$sort_data = array(
			'fd.name',
			'fs.price',
			'rating',
			'f.sort_order'
		);
		
		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			if ($data['sort'] == 'fd.name' || $data['sort'] == 'f.model') {
				$sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
			} else {
				$sql .= " ORDER BY " . $data['sort'];
			}
		} else {
			$sql .= " ORDER BY f.sort_order";	
		}
		
		if (isset($data['order']) && ($data['order'] == 'DESC')) {
			$sql .= " DESC";
		} else {
			$sql .= " ASC";
		}
	
		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}				

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}	
		
			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}

		$food_data = array();
		
		$query = $this->db->query($sql);
		
		foreach ($query->rows as $result) { 		
			$food_data[$result['food_id']] = $this->getFood($result['food_id']);
		}
		
		return $food_data;
	}
	
	public function getLatestFoods($limit) {
		$food_data = $this->cache->get('food.latest.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_restaurant_id') . '.' . (int)$limit);

		if (!$food_data) { 
			$query = $this->db->query("SELECT f.food_id FROM " . DB_PREFIX . "food f WHERE f.restaurant_id = '" . (int)$this->config->get('config_restaurant_id') . "' AND f.status = '1' AND f.date_available <= NOW() ORDER BY f.date_added DESC LIMIT " . (int)$limit);
		 	 
			foreach ($query->rows as $result) {
				$food_data[$result['food_id']] = $this->getFood($result['food_id']);
			}
			
			$this->cache->set('food.latest.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_restaurant_id') . '.' . (int)$limit, $food_data);
		}
		
		return $food_data;
	}
	
	public function getPopularFoods($limit) {
		$food_data = array();
		
		$query = $this->db->query("SELECT f.food_id FROM " . DB_PREFIX . "food f WHERE f.restaurant_id = '" . (int)$this->config->get('config_restaurant_id') . "' AND f.status = '1' AND f.date_available <= NOW() ORDER BY f.viewed, f.date_added DESC LIMIT " . (int)$limit);
		
		foreach ($query->rows as $result) { 		
			$food_data[$result['food_id']] = $this->getFood($result['food_id']);
		}
					 	 		
		return $food_data;
	}

	public function getBestSellerFoods($limit) {
		$food_data = $this->cache->get('food.bestseller.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_restaurant_id') . '.' . (int)$limit);

		if (!$food_data) { 
			$food_data = array();
			
			$query = $this->db->query("SELECT om.food_id, COUNT(*) AS total FROM " . DB_PREFIX . "order_food om LEFT JOIN `" . DB_PREFIX . "order` o ON (om.order_id = o.order_id) LEFT JOIN `" . DB_PREFIX . "food` f ON (om.food_id = f.food_id) WHERE f.restaurant_id = '" . (int)$this->config->get('config_restaurant_id') . "' AND o.order_status_id > '0' AND f.status = '1' AND f.date_available <= NOW() GROUP BY om.food_id ORDER BY total DESC LIMIT " . (int)$limit);
			
			foreach ($query->rows as $result) { 		
				$food_data[$result['food_id']] = $this->getFood($result['food_id']);
			}
			
			$this->cache->set('food.bestseller.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_restaurant_id') . '.' . (int)$limit, $food_data);
		}
		
		return $food_data;
	}
	
	public function getFoodAttributes($food_id) {
		$food_attribute_group_data = array();
		
		$food_attribute_group_query = $this->db->query("SELECT ag.attribute_group_id, agd.name FROM " . DB_PREFIX . "food_attribute ma LEFT JOIN " . DB_PREFIX . "attribute a ON (ma.attribute_id = a.attribute_id) LEFT JOIN " . DB_PREFIX . "attribute_group ag ON (a.attribute_group_id = ag.attribute_group_id) LEFT JOIN " . DB_PREFIX . "attribute_group_description agd ON (ag.attribute_group_id = agd.attribute_group_id) WHERE ma.food_id = '" . (int)$food_id . "' AND agd.language_id = '" . (int)$this->config->get('config_language_id') . "' GROUP BY ag.attribute_group_id ORDER BY ag.sort_order, agd.name");
		
		foreach ($food_attribute_group_query->rows as $food_attribute_group) {
			$food_attribute_data = array();
			
			$food_attribute_query = $this->db->query("SELECT a.attribute_id, ad.name, ma.text FROM " . DB_PREFIX . "food_attribute ma LEFT JOIN " . DB_PREFIX . "attribute a ON (ma.attribute_id = a.attribute_id) LEFT JOIN " . DB_PREFIX . "attribute_description ad ON (a.attribute_id = ad.attribute_id) WHERE ma.food_id = '" . (int)$food_id . "' AND a.attribute_group_id = '" . (int)$food_attribute_group['attribute_group_id'] . "' AND ad.language_id = '" . (int)$this->config->get('config_language_id') . "' AND ma.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY a.sort_order, ad.name");
			
			foreach ($food_attribute_query->rows as $food_attribute) {
				$food_attribute_data[] = array(
					'attribute_id' => $food_attribute['attribute_id'],
					'name'         => $food_attribute['name'],
					'text'         => $food_attribute['text']		 	
				);
			}
			
			$food_attribute_group_data[] = array(
				'attribute_group_id' => $food_attribute_group['attribute_group_id'],
				'name'               => $food_attribute_group['name'],
				'attribute'          => $food_attribute_data
			);			
		}
		
		return $food_attribute_group_data;
	}
			
	public function getFoodOptions($food_id) {
		$food_option_data = array();

		$food_option_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "food_option po LEFT JOIN `" . DB_PREFIX . "option` o ON (po.option_id = o.option_id) LEFT JOIN " . DB_PREFIX . "option_description od ON (o.option_id = od.option_id) WHERE po.food_id = '" . (int)$food_id . "' AND od.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY o.sort_order");
		
		foreach ($food_option_query->rows as $food_option) {
			if ($food_option['type'] == 'select' || $food_option['type'] == 'radio' || $food_option['type'] == 'checkbox' || $food_option['type'] == 'image') {
				$food_option_value_data = array();
			
				$food_option_value_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "food_option_value mov LEFT JOIN " . DB_PREFIX . "option_value ov ON (mov.option_value_id = ov.option_value_id) LEFT JOIN " . DB_PREFIX . "option_value_description ovd ON (ov.option_value_id = ovd.option_value_id) WHERE mov.food_id = '" . (int)$food_id . "' AND mov.food_option_id = '" . (int)$food_option['food_option_id'] . "' AND ovd.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY ov.sort_order");
				
				foreach ($food_option_value_query->rows as $food_option_value) {
					$food_option_value_data[] = array(
						'food_option_value_id' => $food_option_value['food_option_value_id'],
						'option_value_id'         => $food_option_value['option_value_id'],
						'name'                    => $food_option_value['name'],
						'image'                   => $food_option_value['image'],
						'price'                   => $food_option_value['price'],
						'price_prefix'            => $food_option_value['price_prefix']
					);
				}
									
				$food_option_data[] = array(
					'food_option_id' => $food_option['food_option_id'],
					'option_id'         => $food_option['option_id'],
					'name'              => $food_option['name'],
					'type'              => $food_option['type'],
					'option_value'      => $food_option_value_data,
					'required'          => $food_option['required']
				);
			} else {
				$food_option_data[] = array(
					'food_option_id' => $food_option['food_option_id'],
					'option_id'         => $food_option['option_id'],
					'name'              => $food_option['name'],
					'type'              => $food_option['type'],
					'option_value'      => $food_option['option_value'],
					'required'          => $food_option['required']
				);				
			}
      	}
		
		return $food_option_data;
	}
		
	public function getFoodImages($food_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "food_image WHERE food_id = '" . (int)$food_id . "' ORDER BY sort_order ASC");

		return $query->rows;
	}
		
	public function getFoodTags($food_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "food_tag WHERE food_id = '" . (int)$food_id . "' AND language_id = '" . (int)$this->config->get('config_language_id') . "'");

		return $query->rows;
	}
	
	public function getMenus($food_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "food_to_menu WHERE food_id = '" . (int)$food_id . "'");
		
		return $query->rows;
	}	
		
	public function getTotalFoods($data = array()) {
		$sql = "SELECT COUNT(DISTINCT f.food_id) AS total FROM " . DB_PREFIX . "food f LEFT JOIN " . DB_PREFIX . "food_description fd ON (f.food_id = fd.food_id)";

		if (!empty($data['filter_menu_id'])) {
			$sql .= " LEFT JOIN " . DB_PREFIX . "food_to_menu f2m ON (f.food_id = f2m.food_id)";			
		}
		
		if (!empty($data['filter_tag'])) {
			$sql .= " LEFT JOIN " . DB_PREFIX . "food_tag mt ON (f.food_id = mt.food_id)";			
		}
					
		$sql .= " WHERE f.restaurant_id = '" . (int)$this->config->get('config_restaurant_id') . "' AND fd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND f.status = '1' AND f.date_available <= NOW()";
		
		if (!empty($data['filter_name']) || !empty($data['filter_tag'])) {
			$sql .= " AND (";
								
			if (!empty($data['filter_name'])) {
				$implode = array();
				
				$words = explode(' ', $data['filter_name']);
				
				foreach ($words as $word) {
					if (!empty($data['filter_description'])) {
						$implode[] = "LCASE(fd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%' OR LCASE(fd.description) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
					} else {
						$implode[] = "LCASE(fd.name) LIKE '%" . $this->db->escape(utf8_strtolower($word)) . "%'";
					}				
				}
				
				if ($implode) {
					$sql .= " " . implode(" OR ", $implode) . "";
				}
			}
			
			if (!empty($data['filter_name']) && !empty($data['filter_tag'])) {
				$sql .= " OR ";
			}
			
			if (!empty($data['filter_tag'])) {
				$implode = array();
				
				$words = explode(' ', $data['filter_tag']);
				
				foreach ($words as $word) {
					$implode[] = "LCASE(mt.tag) LIKE '%" . $this->db->escape(utf8_strtolower($data['filter_tag'])) . "%' AND mt.language_id = '" . (int)$this->config->get('config_language_id') . "'";
				}
				
				if ($implode) {
					$sql .= " " . implode(" OR ", $implode) . "";
				}
			}
		
			$sql .= ")";
		}
		
		if (!empty($data['filter_menu_id'])) {
			if (!empty($data['filter_sub_menu'])) {
				$implode_data = array();
				
				$implode_data[] = "f2m.menu_id = '" . (int)$data['filter_menu_id'] . "'";
				
				$this->load->model('menu/menu');
				
				$categories = $this->model_menu_menu->getMenusByParentId($data['filter_menu_id']);
					
				foreach ($categories as $food_id) {
					$implode_data[] = "f2m.menu_id = '" . (int)$food_id . "'";
				}
							
				$sql .= " AND (" . implode(' OR ', $implode_data) . ")";			
			} else {
				$sql .= " AND f2m.menu_id = '" . (int)$data['filter_menu_id'] . "'";
			}
		}		
		
		$query = $this->db->query($sql);
		
		return $query->row['total'];
	}
			
	public function getTotalFoodSpecials() {
		$query = $this->db->query("SELECT COUNT(DISTINCT fs.food_id) AS total FROM " . DB_PREFIX . "food_special fs LEFT JOIN " . DB_PREFIX . "food f ON (fs.food_id = f.food_id) WHERE f.restaurant_id = '" . (int)$this->config->get('config_restaurant_id') . "' AND f.status = '1' AND f.date_available <= NOW() AND ((fs.date_start = '0000-00-00' OR fs.date_start < NOW()) AND (fs.date_end = '0000-00-00' OR fs.date_end > NOW()))");
		
		if (isset($query->row['total'])) {
			return $query->row['total'];
		} else {
			return 0;	
		}
	}	
}
?>